﻿ALTER TABLE dbo.tUsers
ADD EmploymentTypeId INT NOT NULL  CONSTRAINT [df_tUsers_EmploymentTypeId] DEFAULT 2

ALTER TABLE dbo.tUsers
ADD CONSTRAINT FK_tUsers_tEmploymentType_EmploymentTypeId FOREIGN KEY (EmploymentTypeId)
REFERENCES tEmploymentType(EmploymentTypeId);


CREATE PROCEDURE usp_GetCompanyHolidays ( @companyId INT )
AS
    BEGIN
        SELECT  TH.holidayName ,
                CONVERT(DATE, CONVERT(VARCHAR(4), DATEPART(YYYY, GETDATE()))
                + '/' + CONVERT(VARCHAR(2), TH.holidayMonth) + '/'
                + CONVERT(VARCHAR(2), TH.holidayDate)) AS HolidayDate
        FROM    dbo.tHolidays AS TH
                JOIN dbo.tCompanyHoliday AS TCH ON TCH.holidayId = TH.holidayId
                JOIN dbo.tCompanies AS TC ON TC.companyId = TCH.companyId
        WHERE   TC.companyId = @companyId
    END

CREATE PROCEDURE usp_GetCompanyHolidaysForEmployee ( @companyId INT, @EmployeeId INT)
AS
    BEGIN
        SELECT  TH.holidayName AS HolidayName ,
                CONVERT(DATE, CONVERT(VARCHAR(4), DATEPART(YYYY, GETDATE()))
                + '/' + CONVERT(VARCHAR(2), TH.holidayMonth) + '/'
                + CONVERT(VARCHAR(2), TH.holidayDate)) AS HolidayDate
        FROM    dbo.tHolidays AS TH
                JOIN dbo.tCompanyHoliday AS TCH ON TCH.holidayId = TH.holidayId
                JOIN dbo.tCompanies AS TC ON TC.companyId = TCH.companyId
				JOIN dbo.tCompanyEmployeeHolidayPaid AS TCEHP ON TCEHP.companyId = TC.companyId
				JOIN dbo.tUsers AS TU ON TU.companyId = TC.companyId
				JOIN dbo.tEmploymentType AS TET ON  TET.EmploymentTypeId = TU.EmploymentTypeId
        WHERE   TC.companyId = @companyId
		AND		TU.userId = @EmployeeId
		AND TCEHP.employmentTypeId = TU.EmploymentTypeId 
		AND TCEHP.isHolidayPaid = 1
		ORDER BY  CONVERT(DATE, CONVERT(VARCHAR(4), DATEPART(YYYY, GETDATE()))
                + '/' + CONVERT(VARCHAR(2), TH.holidayMonth) + '/'
                + CONVERT(VARCHAR(2), TH.holidayDate))
END


CREATE TABLE tHolidayAndType
    (
      holidayId INT ,
      typeId INT
    )

USE [PunchClock]
GO

ALTER TABLE [dbo].[tHolidays] DROP CONSTRAINT [fk_tHolidays_holidayTypeId]
GO



ALTER TABLE dbo.tHolidays
DROP COLUMN holidayTypeId